package com.webvihar.access2db.services;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.QueryParam;
import javax.ws.rs.core.MediaType;

import com.google.gson.Gson;
import com.webvihar.access2db.model.Header;
import com.webvihar.access2db.model.JsonObject;
import com.webvihar.access2db.model.Table;
import com.webvihar.access2db.utils.Base64Coder;


@Path("/runSelectQuery")
public class ExecuteSelectQuery {

	// This method is called if Application/JSON is request
	@POST
	@Produces(MediaType.APPLICATION_JSON)
	public String runResponse(@QueryParam("encoded")Boolean base64encode, @QueryParam("host")String host, @QueryParam("port")String port, @QueryParam("username")String username, @QueryParam("password")String password, @QueryParam("dbname")String dbname, @QueryParam("query")String query) {
		Table table = null;			
		String meta = "";
		String error = "";
		// Decode the data if it is in encoded format
		if(base64encode) {
			host = Base64Coder.decodeString(host);
			port = Base64Coder.decodeString(port);
			username = Base64Coder.decodeString(username);
			password = Base64Coder.decodeString(password);
			dbname = Base64Coder.decodeString(dbname);
			query = Base64Coder.decodeString(query);
		}
		try {
			System.out.println("SELCET");
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			String conString = "jdbc:mysql://"+host+":"+port+"/"+dbname;
			System.out.println("Connection String\t:\t"+conString);
			System.out.println("Query\t:\t"+query);
			Connection con = DriverManager.getConnection(conString, username, password);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			table = new Table(getHeaders(rs), getData(rs));
		} catch (SQLException e) {
			error = e.getMessage();
		} catch(Exception e) {
			error = e.getMessage();
		}
		JsonObject<Table> response = new JsonObject<Table>(0, table, meta, error);
		Gson gson = new Gson();
		return gson.toJson(response);
	}

	public Header[] getHeaders(ResultSet rs) throws SQLException {
		ResultSetMetaData rsmeta = rs.getMetaData();
		Header [] headers = new Header[rsmeta.getColumnCount()];
		for(int i=1; i<=headers.length; i++) {
			Header header = new Header(rsmeta.getColumnName(i), rsmeta.getColumnTypeName(i));
			headers[i-1] = header;
		}
		return headers;
	}

	public String[][] getData(ResultSet rs) throws SQLException {
		int noOfColumns = rs.getMetaData().getColumnCount();
		List<String[]> dataList = new ArrayList<String[]>();
		while(rs.next()) {
			String [] row = new String[noOfColumns];
			for(int i=1; i<=noOfColumns; i++) {
				row[i-1] = rs.getString(i);
			}
			dataList.add(row);
		}

		// convert list to array
		String[][] data = new String[dataList.size()][noOfColumns];
		for(int i=0; i<dataList.size(); i++) {
			data[i] = dataList.get(i);
		}
		return data;
	}
}
